package com.registerserver.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.registerserver.dto.SubjectAndMark;
import com.registerserver.dto.Units;
import com.registerserver.model.Course;
import com.registerserver.model.Student;
import com.registerserver.model.Studying;
import com.registerserver.model.Subject;
import com.registerserver.service.DateFactory;

public class StudyingDaoImpl implements StudyingDao{
	Connection con = null;
	private CourseDaoImpl courseDaoImpl;
	
	public StudyingDaoImpl() {
			con = ConnectionFactory.createConnection();
			
			courseDaoImpl = new CourseDaoImpl();
	}
	
	@Override
	public int getSemester(String StudentID) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<Subject> getSubjects(String studentID, int semester) throws SQLException {
		String sql = "{call getSubjects(?,?)}";
		
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		call.setInt(2, semester);
		
		List<Subject> list = new ArrayList<>();
		
		ResultSet result = call.executeQuery();
		
		if (result!=null){
			while(result.next()){
				Subject subject = new Subject();
				Course course = new Course();
//				subject.setSubjectName("")
				list.add(subject);
			}
		}
		return null;
	}

	// Diem sinh vien se cap nhat trong qua trinh hoc bang phuong thuc updateMark
	@Override
	public int addNewStudying(String subjectID, Course course, Student student, Date registerDate, double mark) throws SQLException {
		int countOfStudent = countStudentRegistered(course.getCourseID(), subjectID);
		if (countOfStudent==getMaxSizeOfCourse())
			return 2;// lop hoc da du sinh vien
		else {
			/**
			 * in argStudentID varchar(10),
				in argSubjectID varchar(10),
				in argCourseID varchar(10),
				in argMark float,
				in argRegisterDate date
			 */
			String sql = "{call addNewStudying(?,?,?,?,?)}";
			CallableStatement call = con.prepareCall(sql);
			call.setString(2, subjectID);
			call.setString(1, student.getID());
			call.setDouble(4,mark);
			call.setString(3, course.getCourseID());
			call.setDate(5,registerDate);
			int affectRow = call.executeUpdate();
			if (affectRow>0)
				return 1;
		}
		return 0;
	}

	@Override
	public boolean updateStudying(String subjectID, Course course, Student student) throws SQLException{
		String sql = "{call updateStudying(?,?,?,,)}";
		return false;
	}

	//???? khi cap nhat diem mot mon hoc cho sinh vien, ko the doi hoi nguoi nhap phai chon ca ma sinh vien, ma mon , ma hoc phan 
	@Override
	public boolean updateMark(String studentID,String subjectID,String courseID)
			throws SQLException {
		String sql = "{call updateMark(?,?,?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		call.setString(2, subjectID);
		call.setString(3, courseID);
		return call.executeUpdate()>0;
	}

	@Override
	public int deleteStudying(String studentID, String subjectID, String courseID)
			throws SQLException {
		// kiem tra neu diem >5.5 => khong duoc hoc cai thien=> khong duoc huy
		// kiem tra neu ngay dang ki > ngay het han dk=> khong duoc huy
		if (getMark()>5.5)
			return 0;// khong dang ki thanh cong do khong the hoc cai thien vi diem so >5.5
		else if(courseDaoImpl.getExpiredDate(courseID).before(DateFactory.getCurrentDate())){
			return 1; // khong thanh cong do het han dang ki hoc phan
		}
		String sql = "{call deleteStudying(?,?,?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		call.setString(2, subjectID);
		call.setString(3, courseID);
		int result = call.executeUpdate();
		if (result>0)
		return 2; // thanh cong
		else return 3;// khong thanh cong
	}

	@Override
	public double getOverAllMarkOfStudent(String studentID) throws SQLException {
		double mark = 0.0;
		String sql = "{call calcOverAllMarkByStdID(?)}";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		ResultSet result = call.executeQuery();
		if(result.next())
			mark = result.getDouble(1);
		return mark;
	}

	@Override
	public int countStudentRegistered(String courseID,String subjectID) throws SQLException {
		String sql = "call countStudentsRegistered(?,?)";
		
		CallableStatement call= con.prepareCall(sql);
		call.setString(1, subjectID);
		call.setString(2, courseID);
		ResultSet result = call.executeQuery();
		if (result.next())
			return result.getInt(1);
		return 0;
	}

	@Override
	public int getMaxSizeOfCourse() {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public double getMark() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<SubjectAndMark> getMarkOfAllSubject(String studentID) throws SQLException {
		List<SubjectAndMark> list = new ArrayList<>();
		String sql = "call getCourseAndMarkByStdID(?)";
		CallableStatement call = con.prepareCall(sql);
		call.setString(1, studentID);
		ResultSet result = call.executeQuery();
		while(result.next()){
			SubjectAndMark subject = new SubjectAndMark();
			subject.setMark(result.getDouble("Mark"));
			subject.setSubjectName(result.getString("SubjectName"));
			subject.setUnits(result.getInt("Units"));
			subject.setCourseID(result.getString("CourseID"));
			list.add(subject);
		}
		return list;
	} 
}
